package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.DatetimeKit;
import cn.com.dashihui.kit.DoubleKit;
import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.AdSeller;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class UserService {
	
	public boolean addUser(User newObject){
		return newObject.save();
	}

//	public int saveVip(String card,String aname,String aphone,String date,String storeNo,String ssx) {
//		return Db.use("sqlserver").update("insert into VipCards (CardNo,HandOutDate,ValidDate,HolderName,HolderTel,MakeDate,ClearDate,HandOutDept,CardTypeID,sex) values(?,?,?,?,?,?,?,?,?,?)", card,date,"2099-12-31",aname,aphone,date,date,storeNo,1,ssx);
//	}
//
//	public Record findVipId(String aphone) {
//		return Db.use("sqlserver").findFirst("select CardID from VipCards where HolderTel=?",aphone);
//	}
//	public int saveVipSum(Integer id, String time) {
//		return Db.use("sqlserver").update("insert into VIPIntegralSummary (CardID,LastTime) values(?,?)",id,time);
//	}
//	public void setPhone(String phone,String oldPhone){
//		Db.use("sqlserver").update("update VipCards set HolderTel=? where HolderTel=?",phone,oldPhone);
//	}
	public User findById(int id){
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount,  "
				+ " (year(now())-year(A.birthday)-1) + ( DATE_FORMAT(A.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age "
				+ " FROM t_bus_user A "
				+ "left join t_bus_user_wx B on A.id=B.userid "
				+ "left join t_bus_seller Y on Y.id = A.toBossThreeId "
				+ "left join t_dict_store Z on Z.id = A.toBossTwoId "
 				+ "left join t_auth_admin_seller X on X.id = A.toBossOneId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from "
				+ " t_bus_order where orderState=2 group by userid) C on C.userid= B.id WHERE A.id=?",id);
	}
	public User findByWxUser(int id){
		return User.me().findFirst("select wx.openid from t_bus_user u left join t_bus_user_wx wx on u.id=wx.userid WHERE u.id=?",id);	
	}
	/**
	 * 根据手机号查询用户的openid
	 * @param phoneList
	 * @return List<Record>
	 */
	public List<Record> findWxOpenIdDao(List<String> phoneList) {
		StringBuffer buffer = new StringBuffer("select wx.openid from t_bus_user u left join t_bus_user_wx wx on u.id=wx.userid WHERE 1=1 and");
		for(int i=0;i<phoneList.size();i++){
			if(i==0){
				buffer.append(" u.phone='"+phoneList.get(i)+"'");
			}else{
				buffer.append(" or u.phone='"+phoneList.get(i)+"'");
			}
		}
		return Db.find(buffer.toString());
	}
	
	/**
	 * 会员信息
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public Page<Record> findByPage(int pageNum, int pageSize,String name, String phone, String card, String sBeginDate, String sEndDate,
			String address, int toBossThreeId, int toBossTwoId, int toBossOneId, String taici, String state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user B on B.id = A.inviteid "
				+ "left join t_bus_seller C on C.id = A.toBossThreeId "
				+ "left join t_dict_store D on D.id = A.toBossTwoId "
				+ "left join t_auth_admin_seller E on E.id = A.toBossOneId "
				+ "left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)F on F.inviteid= A.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.*,B.name as pareName,C.sellerName ,D.title,E.trueName,F.userNum ", sBuffer.toString(),params.toArray());

	}
	/**
	 * 邀请奖励
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public Page<Record> findByPageRewards(int pageNum, int pageSize, String phone, String card, String sBeginDate, String sEndDate, String taici){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "inner join t_bus_user_value_log L on L.userid = A.id "
				+ "left join (select userid,createDate,sum(sppoint) as totalPoint from  t_bus_user_value_log where flag = 11  group by userid) D on D.userid= A.id "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount,sum(gbprice) as totalMoney from  t_bus_order_gb where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		System.out.println(phone);
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND L.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(L.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(L.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		sBuffer.append(" AND L.flag = 11 group by A.id ORDER BY A.createDate DESC,A.id desc");
		//ifnull(D.totle,0) allCount, ifnull(D.cou, 0) AS excnum ,
		return Db.paginate(pageNum, pageSize, "SELECT A.*, ifnull(C.countcum, 0) AS countnum, ifnull(C.totalMoney, 0) AS totalMoney,ifnull(D.totalPoint, 0) AS totalPoint", sBuffer.toString(),params.toArray());
	}
	/**
	 * 团队会员信息
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public Page<Record> findByPageteam(int id,int pageNum, int pageSize, String phone, String card, String sBeginDate, String sEndDate, 
			String address, int toBossThreeId, int toBossTwoId, int toBossOneId, String taici, String state){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}
		sBuffer.append(" and A.inviteid in ('"+id+"')");
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		//ifnull(D.totle,0) allCount, ifnull(D.cou, 0) AS excnum ,
		return Db.paginate(pageNum, pageSize, "SELECT A.*, ifnull(C.countcum, 0) AS countnum,CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName, SE.sellerName AS sellerName, ST.title AS storeName "
				, sBuffer.toString(),params.toArray());
	}
	
	public boolean findExistsByUsername(String username){
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE username=?",username)!=null;
	}
	
	public boolean findExistsByUsernameWithout(String username,int userid){
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE username=? AND id!=?",username,userid)!=null;
	}

	public User findByPhone(String phone) {
		return User.me().findFirst("select * from t_bus_user where phone=?",phone);
	}

	public List<Store> findStoreList() {
		return Store.me().find("select id,storeid,title,city from t_dict_store");
	}
	/**
	 * 选择分公司
	 * @param store
	 * @return
	 */
	public List<Record> chooseStore(String seller) {
		return Db.find("select id,storeid,title from t_dict_store where sellerId in ('"+seller+"')");
	}
	/**
	 * 选择营业部
	 * @param store
	 * @return
	 */
	public List<Record> chooseDao(String store) {
		return Db.find("select S.id,S.trueName from t_auth_admin_seller as S "
				+ "left join t_auth_admin_roles_seller as R on S.id=R.userid where R.roleid =4 and storeid in ('"+store+"')");
	}
	/**
	 * 选择营业部
	 * @param store
	 * @return
	 */
	public List<Record> findDaoList() {
		return Db.find("select S.id,S.trueName from t_auth_admin_seller as S "
				+ "left join t_auth_admin_roles_seller as R on S.id=R.userid where R.roleid =4 ");
	}

	/**
	 * 根据storeid查询store
	 * @param store
	 * @return
	 */
	public Store findStoreById(String store) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",store);
	}
	/**
	 * 根据主键查询store
	 * @param store
	 * @return
	 */
	/*public Store findStoreById(String store) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",store);
	}*/
	
	/**
	 * 检索分公司列表（特殊账号限制为只检索郑州地区的分公司）
	 * @param store
	 * @return
	 */
	public List<Store> lookStoreByIds() {
		return Store.me().find("select title from t_dict_store where city=187");
	}
	
	public List<Store> findStoreByIds(String id) {
		return Store.me().find("select id,title from t_dict_store where id in ('"+id+"')");
	}
	
	public User findUserById(int id) {
		return User.me().findFirst("select *,(year(now())-year(birthday)-1) + ( DATE_FORMAT(birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age from t_bus_user where id=?",id);
	}
	/**
	 * 根据手机号查询后台账户
	 * @return
	 */
	public AdSeller findAdSellerByPhone(String phone) {
		return AdSeller.me().findFirst("select * from t_auth_admin_seller where phonenumber = ?",phone);
	}
	public List<Record> findUserList(String store) {
		return Db.find("select A.* from t_auth_admin_seller A left join t_dict_store B on A.storeid=B.storeid where B.title=?",store);
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "团购邀请奖励明细", titles, listz);
	}
	public void exportVip(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "会员积分统计", titles, listz);
	}
	public void exportHistory(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "历史业绩查询", titles, listz);
	}
	/**
	 * 
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param address
	 * @param id
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public List<Record> findExcelByPage(String phone, String card, String sBeginDate, String sEndDate, String beginDate,
			String endDate,String address,String id, String dao, String taici, String state) {
//		StringBuffer sBuffer = new StringBuffer("SELECT A.* ,ifnull(D.cou, 0) AS excnum , ifnull(C.countcum, 0) AS countnum ,ifnull(D.totle,0) allCount  "
//				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where payState=2 group by userid) C on C.userid= B.id "
//				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		//ifnull(D.totle,0) allCount, ifnull(D.cou, 0) AS excnum ,
		StringBuffer sBuffer = new StringBuffer("SELECT A.*, ifnull(C.countcum, 0) AS countnum,CNum.userNum ,G.id AS pareId, G.name AS pareName, S.trueName"
				+ ", SE.sellerName AS sellerName, ST.title AS storeName FROM t_bus_user A "
				+ "left join t_bus_user G on G.id=A.inviteid "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_bus_seller SE on SE.id=A.toBossThreeId "
				+ "left join t_dict_store ST on ST.id=A.toBossTwoId "
				+ "left join (select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)CNum on  CNum.inviteid= A.id  "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
//		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
//			sBuffer.append(" AND A.createDate BETWEEN ? AND ?");
//			params.add(beginDate);
//			params.add(endDate);
//		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
//			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
//			params.add(beginDate);
//		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
//			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
//			params.add(endDate);
//		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(address)){
			sBuffer.append(" AND A.address like ?");
			params.add("%"+address+"%");
		}
		if(StrKit.notBlank(id)){
			sBuffer.append(" and A.toBossTwoId in ('"+id+"')");
		}
		if(StrKit.notBlank(dao)){
			sBuffer.append(" and A.toBossOneId in ('"+dao+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}

		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find( sBuffer.toString(),params.toArray());
	}
	/**
	 * 
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param id
	 * @param taici
	 * @return
	 */
	public List<Record> doExcelRewards(String phone, String card, String sBeginDate, String sEndDate
			,String id, String taici) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*, ifnull(C.countcum, 0) AS countnum, ifnull(C.totalMoney, 0) AS totalMoney,ifnull(D.totalPoint, 0) AS totalPoint"
				+" FROM t_bus_user A "
				+ "inner join t_bus_user_value_log L on L.userid = A.id "
				+ "left join (select userid,createDate,sum(sppoint) as totalPoint from  t_bus_user_value_log where flag = 11  group by userid) D on D.userid= A.id "
				+ "left join (select userid,payDate,count(id) as countcum,sum(amount) as allCount,sum(gbprice) as totalMoney from  t_bus_order_gb where orderState NOT in(1,5,7,9)  group by userid) C on C.userid= A.id "
				+ " LEFT JOIN (select phone,COUNT(id) as coun from t_vip_user_orderdail GROUP BY phone) E on E.phone=A.phone where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND L.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(L.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(L.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		sBuffer.append(" AND L.flag = 11 group by A.id ORDER BY A.createDate DESC,A.id desc");
		return Db.find( sBuffer.toString(),params.toArray());
	}
	/**
	 * 
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param toBossTwoId
	 * @return
	 */
public Page<Record> findByVipPages(int pageNum, int pageSize,String phone, String card, int toBossTwoId) {
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "left join t_auth_admin_seller S on S.id=A.toBossOneId "
				+ "left join t_dict_store ST on ST.id = A.toBossTwoId where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId=?");
			params.add(toBossTwoId);
		}
			sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.paginate(pageNum, pageSize, "SELECT A.* ,ST.title AS storeName, S.trueName", sBuffer.toString(),params.toArray());
	}
/**
 * 排序
 * @param pageNum
 * @param pageSize
 * @param index
 * @param phone
 * @param card
 * @param title
 * @return
 */
public Page<Record> findByVipPages1(int pageNum, int pageSize,String index,String phone, String card, String title) {
	StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A where 1=1 ");
	List<Object> params = new ArrayList<Object>();
	if(StrKit.notBlank(phone)){
		sBuffer.append(" AND A.phone like ?");
		params.add("%"+phone+"%");
	}
	if(StrKit.notBlank(card)){
		sBuffer.append(" and A.card=?");
		params.add(card);
	}
	if(StrKit.notBlank(title)){
		sBuffer.append(" and A.store=?");
		params.add(title);
	}
	if(!StrKit.isBlank(index)){
		switch (index) {
		case "1"://商品
			sBuffer.append(" ORDER BY A.sppoint DESC");
			break;
		case "2":
			sBuffer.append(" ORDER BY A.sppoint ASC");
			break;
		case "3"://健康
			sBuffer.append(" ORDER BY A.jkpoint DESC");
			break;
		case "4":
			sBuffer.append(" ORDER BY A.jkpoint ASC");
			break;	
		case "5"://教育
			sBuffer.append(" ORDER BY A.jypoint DESC");
			break;
		case "6":
			sBuffer.append(" ORDER BY A.jypoint ASC");
			break;
	     }	
	}
	return Db.paginate(pageNum, pageSize, "SELECT A.* ", sBuffer.toString(),params.toArray());
}
	public List<Record> findExcelVipByPage(String phone, String card, String title) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.*,ST.title AS storeName, S.trueName FROM t_bus_user A left join t_auth_admin_seller S on S.id=A.toBossOneId left join t_dict_store ST on ST.id = A.toBossTwoId  where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and A.store=?");
			params.add(title);
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}

	public List<Record> findOffJfOrderList(String phone) {
		return Db.find("select * from t_vip_user_orderdail where phone=?",phone);
	}
	public List<Record> findOrderList(int id) {
		return Db.find("select A.*,I.title,J.name as egeName,G.name from t_bus_user_value_log A "
				+ "left join t_bus_order_list E on A.fromOrderNum = E.orderNum "
				+ "left join t_bus_order_act F on F.orderNum = A.fromOrderNum "
				+ "left join t_bus_ege_goods J on J.id = F.goodsid "
				+ "left join t_bus_goods G on G.id = E.goodsid "
				+ "left join t_bus_goods_store_rel  H on H.goodstore = G.good_store "
				+ "left join t_dict_store I on I.storeid = H.storeid "
				+ "left join t_bus_user C on C.id=A.userid "
				+ "where 1 = 1 and C.id=? order by A.createDate asc ",id);
	}

	public List<Record> findMesageList(String phone, String card, String sBeginDate,String sEndDate, String beginDate,
			String endDate, String title, String dao, String taici, String state) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join "
				+ "(select userid,count(id) as countcum,sum(amount) as allCount from  t_bus_order where orderState=2 group by userid) C on C.userid= B.id where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND A.createDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(StrKit.notBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&StrKit.notBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.createDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(title)){
			sBuffer.append(" and A.storeName in ('"+title+"')");
		}
		if(StrKit.notBlank(dao)){
			sBuffer.append(" and A.toBossOneId in ('"+dao+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}

		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		return Db.find(sBuffer.toString(),params.toArray());
	}
	/**
	 * 生产期
	 * @param times
	 * @return
	 */
	public List<User> findByTime(String times) {
		return User.me().find("select * from t_bus_user where state=2 and babythday=? ",times);
	}

	/**
	 * 查询所有营业部
	 * @return
	 */
	public List<AdSeller> findAdSeller() {
		return AdSeller.me().find("select * from t_auth_admin_seller");
	}
	/**
	 * 查询所有营业部
	 * @return
	 */
	public AdSeller findAdSellerByName(String trueName) {
		return AdSeller.me().findFirst("select id from t_auth_admin_seller where trueName = ?",trueName);
	}
	/**
	 * 根据userid,获取角色
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid){
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where B.userid=?" ,userid);
	}

	/**
	 * 查询所有公司
	 * @return
	 */
	public List<Seller> findAllSeller() {
		return Seller.me().find("select * from t_bus_seller");
	}
	/**
	 * 根据sellerid查询serller
	 */
	public Seller findSellerById(String sellerid) {
		return Seller.me().findFirst("select id from t_bus_seller where sellerId=?",sellerid);
	}
	/**
	 * 根据storeid查询store
	 * @param store
	 * @return
	 */
	public Store findStoreById1(String store) {
		return Store.me().findFirst("select id from t_dict_store where storeid=?",store);
	}
	
	/**
	 * 查询历史业绩信息
	 * @param pageNum
	 * @param pageSize
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @return
	 */
	public Page<Record> queryHistoryAmountByPage(int pageNum, int pageSize, String card, String name, String phone, String sBeginDate, String sEndDate){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_user A "
				+ "LEFT JOIN (select OA.inviteid ,sum(O.amount) as allCount,count(O.id) as countcum  from t_bus_user OA  left join t_bus_order_gb O   on O.userid = OA.id where O.orderState !=1 and O.orderState !=5 ");
		List<Object> params = new ArrayList<Object>();
		
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND O.payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(O.payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(O.payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		sBuffer.append("  group by OA.inviteid) C on C.inviteid= A.id  "
				+ "LEFT JOIN (select inviteid,count(inviteid) AS userNum from t_bus_user where 1=1 ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		sBuffer.append("  group by inviteid)B on  B.inviteid= A.id "
				+ "LEFT JOIN (select OFIA.inviteid,count(OFI.id) as ficum,sum(OFI.amount) as finum from (SELECT * FROM  t_bus_order_gb WHERE id in (SELECT MIN(id) FROM t_bus_order_gb where orderState NOT in(1,5,7,9) ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("  group by userid) ) OFI left join t_bus_user OFIA on OFI.userid=OFIA.id where 1=1  ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OFIA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OFIA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OFIA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("   group by OFIA.inviteid) D on D.inviteid=A.id "
				+ "LEFT JOIN (select OSEA.inviteid,count(OSE.userid) as seuserNum from  (SELECT userid,count(userid) as count FROM t_bus_order_gb where orderState NOT in(1,5,7,9)   ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		
		
		sBuffer.append(" group by userid having count>1) OSE left join t_bus_user OSEA on OSE.userid=OSEA.id where 1=1  ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OSEA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OSEA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OSEA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("  group by OSEA.inviteid)E on E.inviteid=A.id"
				+ " where 1=1     ");
		
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" and A.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(phone)){
			sBuffer.append(" and A.phone like ?");
			params.add("%"+phone+"%");
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id DESC");
		return Db.paginate(pageNum, pageSize, "SELECT A.id,A.createDate,A.card,A.name,A.phone,A.custName,"
				+ "B.userNum, C.countcum AS countcum,C.allCount AS allCount ,"
				+ "D.ficum,D.finum ,E.seuserNum,(C.countcum-D.ficum)as secum,(C.allCount-D.finum)as senum ", sBuffer.toString(),params.toArray());
	}
	/**
	 * 历史业绩查询导出
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @return
	 */
	public List<Record> findExcelHistory(String card, String sBeginDate, String sEndDate) {
		StringBuffer sBuffer = new StringBuffer("SELECT A.id,A.createDate,A.card,A.name,A.phone,A.custName,"
				+ "B.userNum, C.countcum AS countcum,C.allCount AS allCount ,"
				+ "D.ficum,D.finum ,E.seuserNum,(C.countcum-D.ficum)as secum,(C.allCount-D.finum)as senum "
				+ "FROM t_bus_user A "
				+ "LEFT JOIN (select OA.inviteid ,sum(O.amount) as allCount,count(O.id) as countcum  from t_bus_user OA  left join t_bus_order_gb O   on O.userid = OA.id where O.orderState !=1 and O.orderState !=5 ");
		List<Object> params = new ArrayList<Object>();
		
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND O.payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(O.payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(O.payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		sBuffer.append("  group by OA.inviteid) C on C.inviteid= A.id  "
				+ "LEFT JOIN (select inviteid,count(inviteid) AS userNum from t_bus_user where 1=1 ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		sBuffer.append("  group by inviteid)B on  B.inviteid= A.id "
				+ "LEFT JOIN (select OFIA.inviteid,count(OFI.id) as ficum,sum(OFI.amount) as finum from (SELECT * FROM  t_bus_order_gb WHERE id in (SELECT MIN(id) FROM t_bus_order_gb where orderState NOT in(1,5,7,9) ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("  group by userid) ) OFI left join t_bus_user OFIA on OFI.userid=OFIA.id where 1=1  ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OFIA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OFIA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OFIA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("   group by OFIA.inviteid) D on D.inviteid=A.id "
				+ "LEFT JOIN (select OSEA.inviteid,count(OSE.userid) as seuserNum from  (SELECT userid,count(userid) as count FROM t_bus_order_gb where orderState NOT in(1,5,7,9)   ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND payDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(payDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		
		
		sBuffer.append(" group by userid having count>1) OSE left join t_bus_user OSEA on OSE.userid=OSEA.id where 1=1  ");
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND OSEA.createDate BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OSEA.createDate,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(OSEA.createDate,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		
		sBuffer.append("  group by OSEA.inviteid)E on E.inviteid=A.id"
				+ " where 1=1     ");
		
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id DESC");
		return Db.find( sBuffer.toString(),params.toArray());
	}
	/**
	 * 分页查找提现订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPageMoney(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String name,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_ecard A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "left JOIN t_bus_user_card H on H.userid=F.id "
				+ "left JOIN t_bus_user_bank I on I.id=H.bankid "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user G on F.toBossOneId=G.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND B.id in ('"+storeid+"')");
		}

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		}
		if(StrKit.notBlank(tel)){
			sBuffer.append(" AND F.phone like ?");
			params.add("%"+tel+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" AND F.name like ?");
			params.add("%"+name+"%");
		}
		/*if(StrKit.notBlank(address)){
		sBuffer.append(" AND A.address like ?");
		params.add("%"+address+"%");
		}*/
			sBuffer.append(" AND A.paytype = 6");
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,H.province,H.city,H.bankSite,H.cardNo as bankId,H.phoneNo as phoneNo,H.custName as cardUserName,I.bankName,C.sellerName,B.title as xdstore,F.id as uid,F.name,F.card,F.phone,F.toBossOneId,G.name as jjrName ",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 提现驳回逻辑
	 * @param orderNum
	 * @param describe
	 * @return -1没有这个订单  -2 修改订单状态失败
	 */
	@Before(Tx.class)
	public int doMoneyCancel(String orderNum, String describe) throws Exception{
		//查找订单
		Record ecard = Db.findFirst("SELECT * FROM t_bus_order_ecard WHERE orderNum=? AND  outStarte=?  ",orderNum,1);
		if ( ecard==null ){
			return -1;
		}
		
		//修改订单状态为驳回：t_bus_order_ecard
		ecard.set("outStarte", 3).set("describe", describe).set("createDate", DatetimeKit.getFormatDate(null));
		
		if ( !Db.update("t_bus_order_ecard", ecard) ){
			return -2;
		}
		
		double addmoney = ecard.getDouble("amount");
		int userid = ecard.getInt("userid");
		Record user = Db.findById("t_bus_user", userid);
		//修改t_bus_user  用户余额
		double oldmoney = user.getDouble("money");
		double newmoney = DoubleKit.add(addmoney, oldmoney);
		int count = Db.update("UPDATE t_bus_user SET money=? WHERE id=? AND money=? ",newmoney,userid,oldmoney);
		if ( count != 1){
			throw new RuntimeException("提现订单驳回------修改余额失败");
		}
		
		//记录t_bus_user_money   驳回余额变动
		Record rec = new Record()
				.set("userid", userid)
				.set("amount", addmoney)
				.set("type", 1)
				.set("describe", "提现申请驳回金额返还")
				.set("fromOrderNum", orderNum)
				.set("createDate", DatetimeKit.getFormatDate(null))
				.set("orderType", 3)
				.set("nowAmount", newmoney)
				.set("oldAmount", oldmoney);
		if ( !Db.save("t_bus_user_money", rec)  ){
			throw new RuntimeException("记录用户余额变更失败t_bus_user_money");
		}
		
		return 1;
	}
	/**
	 * 查询提现订单
	 */
	public Record findEcardByorderNum(String orderNum) {
		return Db.findFirst("SELECT * FROM t_bus_order_ecard WHERE orderNum=? ",orderNum);
	}
	/**
	 * 提现审核通过
	 * @param orderNum
	 * @param payNum
	 * @param otherMoney
	 */
	public int doMoneyAccept(String orderNum, String payNum, String otherMoney) {
		//查询订单
		Record ecard = Db.findFirst("SELECT * FROM t_bus_order_ecard WHERE orderNum=? AND  outStarte=?  ",orderNum,1);
		if ( ecard==null ){
			return -1;
		}
		//修改订单状态t_bus_order_ecard
		ecard.set("outStarte",2)
		.set("payNum",payNum)
		.set("otherMoney",Double.parseDouble(otherMoney))
		.set("payDate",DatetimeKit.getFormatDate(null))
		.set("createDate",DatetimeKit.getFormatDate(null));
		if ( !Db.update("t_bus_order_ecard", ecard) ){
			return -2;
		}
		return 1;
	}
	/**
	 * 会员数量统计
	 * @param pageNum
	 * @param pageSize
	 * @param phone
	 * @param card
	 * @param sBeginDate
	 * @param sEndDate
	 * @param beginDate
	 * @param endDate
	 * @param title
	 * @param dao
	 * @param taici
	 * @param state
	 * @return
	 */
	public Record findTotal(String name, String phone, String card, String sBeginDate, String sEndDate,
			String address, int toBossThreeId, int toBossTwoId, int toBossOneId, String taici, String state){
		StringBuffer sBuffer = new StringBuffer("SELECT "
				+ "IFNULL(ROUND(count(amount), 2),0) total,"
				+ "IFNULL(ROUND(count(amount1), 2),0) seller,"
				+ "IFNULL(ROUND(count(amount2), 2),0) store,"
				+ "IFNULL(ROUND(count(amount3), 2),0) jjr "
				+ "from ( select "
				+ "case when A.taici!=0 then A.id else null end as amount, "
				+ "case when A.taici=7 then A.id else null end as amount1, "
				+ "case when A.taici=6 then A.id else null end as amount2, "
				+ "case when A.taici=5 then A.id else null end as amount3 "
				+ "FROM t_bus_user A "
				+ "left join t_bus_user B on B.id = A.inviteid "
				+ "left join t_bus_seller C on C.id = A.toBossThreeId "
				+ "left join t_dict_store D on D.id = A.toBossTwoId "
				+ "left join t_auth_admin_seller E on E.id = A.toBossOneId "
				+ "left join (select id,inviteid,COUNT(inviteid) AS userNum from t_bus_user where 1=1 group by inviteid)F on F.inviteid= A.id "
				+ " where 1=1 ");
		List<Object> params = new ArrayList<Object>();
		if(StrKit.notBlank(phone)){
			sBuffer.append(" AND A.phone like ?");
			params.add("%"+phone+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+name+"%");
		}
		if(StrKit.notBlank(card)){
			sBuffer.append(" and A.card=?");
			params.add(card);
		}
		if(StrKit.notBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND A.birthday BETWEEN ? AND ?");
			params.add(sBeginDate);
			params.add(sEndDate);
		}else if(StrKit.notBlank(sBeginDate)&&StrKit.isBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')>=?");
			params.add(sBeginDate);
		}else if(StrKit.isBlank(sBeginDate)&&StrKit.notBlank(sEndDate)){
			sBuffer.append(" AND DATE_FORMAT(A.birthday,'%Y-%m-%d')<=?");
			params.add(sEndDate);
		}
		if(toBossThreeId!=0){
			sBuffer.append(" and A.toBossThreeId in ('"+toBossThreeId+"')");
		}
		if(toBossTwoId!=0){
			sBuffer.append(" and A.toBossTwoId in ('"+toBossTwoId+"')");
		}
		if(toBossOneId!=0){
			sBuffer.append(" and A.toBossOneId in ('"+toBossOneId+"')");
		}
		if(StrKit.notBlank(taici)){
			sBuffer.append(" and A.taici in ('"+taici+"')");
		}
		if(StrKit.notBlank(state)){
			sBuffer.append(" and A.state in ('"+state+"')");
		}
		sBuffer.append(" ORDER BY A.createDate DESC,A.id desc");
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;
	}
}
